package my.dao.lmpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import my.bean.Cart;
import my.bean.Order;
import my.bean.Tea;
import my.bean.User;
import my.dao.IOrderDao;
import my.dbUtil.DBUtils;

public class IOrderDaoImpl implements IOrderDao {
	private Connection conn;

	public IOrderDaoImpl() {
	}

	public IOrderDaoImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public int addOrder(Order order) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "insert into orders values(order_id.nextval,?,?,?,sysdate)";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, order.getTea().getId());
			prestmt.setInt(2, order.getUser().getId());
			prestmt.setDouble(3, order.getCount());

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("插入失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public int deleteOrderById(int id) throws SQLException {
		PreparedStatement prestmt = null;
		int record = 0;

		try {
			String sql = "delete from orders where order_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			record = prestmt.executeUpdate();

			return record;
		} catch (SQLException e) {
			System.err.println("删除失败!");
			e.printStackTrace();
			throw e;
		} finally {
			DBUtils.close(null, prestmt, null);
		}
	}

	@Override
	public List<Order> findAll() {
		List<Order> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from orders natural join tea natural join users";
			prestmt = conn.prepareStatement(sql);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				Order order = new Order(id, user, tea, count, buyDate);
				list.add(order);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Order> findByUserid(int id) {
		List<Order> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from orders natural join tea natural join users where user_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				Order order = new Order(id, user, tea, count, buyDate);
				list.add(order);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Order> findByTeaId(int id) {
		List<Order> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from orders natural join tea natural join users where tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				Order order = new Order(id, user, tea, count, buyDate);
				list.add(order);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Order> findByTeaIdAndUserID(int uId, int tId) {
		List<Order> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from orders natural join tea natural join users where user_id=? and tea_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, uId);
			prestmt.setInt(2, tId);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				Order order = new Order(id, user, tea, count, buyDate);
				list.add(order);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public List<Order> findByTeaType(int type) {
		List<Order> list = new ArrayList<>();
		PreparedStatement prestmt = null;
		ResultSet rs = null;

		try {
			String sql = "select * from orders natural join tea natural join users where order_type=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, type);

			rs = prestmt.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				Order order = new Order(id, user, tea, count, buyDate);
				list.add(order);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtils.close(null, prestmt, rs);
		}
		return list;
	}

	@Override
	public Order findByOrderId(int id) {
		Order order = null;
		PreparedStatement prestmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from orders natural join tea natural join users where order_id=?";
			prestmt = conn.prepareStatement(sql);
			prestmt.setInt(1, id);
			
			rs = prestmt.executeQuery();
			if(rs.next()) {
				id = rs.getInt("order_id");

				int tid = rs.getInt("tea_id");
				String tName = rs.getString("tea_name");
				String from = rs.getString("tea_from");
				double price = rs.getDouble("tea_price");
				double stock = rs.getDouble("tea_stock");
				String introduction = rs.getString("tea_introduction");
				int type = rs.getInt("tea_type");
				Tea tea = new Tea(tid, tName, from, price, stock, type, introduction);

				int uid = rs.getInt("user_id");
				String account = rs.getString("user_account");
				String password = rs.getString("user_password").trim();
				String email = rs.getString("user_email");
				String phone = rs.getString("user_phone");
				Timestamp regDate = rs.getTimestamp("user_regdate");
				User user = new User(uid, account, password, email, phone, regDate);

				double count = rs.getDouble("count");
				Timestamp buyDate = rs.getTimestamp("buy_date");

				order = new Order(id, user, tea, count, buyDate);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.close(null, prestmt, rs);
		}
		return order;
	}

}
